﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using APUtil;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text.RegularExpressions;

namespace miniBoxClassLibrary
{
    public class clsDataSrc_cshap
    {
        private clsDBUtil oDB = new APUtil.clsDBUtil();
        private SendVerifyCode oSend;
        public void BeginTrans()
        {
            oDB.BeginTrans();
        }

        public void Dispose()
        {
            oDB = null;
            this.Dispose();
        }

        public void Commit()
        {
            oDB.Commit();
        }

        public void RollBack()
        {
            oDB.RollbackTrans();
        }

        /// <summary>
        /// 取得編號
        /// </summary>
        /// <param name="type">類別(例如:儲值,入库,出库,箱號)</param>
        /// <param name="prefix">前置詞(例如:ORDER,REQ,PICK,BOX)</param>
        /// <returns></returns>
        public String Get_SEQNO(string type, string prefix)
        {
            if (prefix.Length > 5) throw new Exception("Prefix Length not more than 5!");
            MemberClass mc = new MemberClass();
            DataSet ds = new DataSet();
            SqlParameter[] param3 = 
                {
                     new SqlParameter("@TYPE",type),
                     new SqlParameter("@PREFIX",prefix),
                     new SqlParameter("@NO_LENGTH",15-prefix.Length)
                };
            try
            {
                ds = oDB.ExecuteSPGetDT("USP_GET_SYS_SEQ_NO", param3);  //取得订单编号
                mc.strRt_Value = ds.Tables[0].Rows[0].ItemArray[0].ToString();
            }
            catch (Exception ex) { return ex.ToString(); }
            return mc.strRt_Value;
        }

        #region 會員登入

        /// <summary>
        /// 取得最新TOKEN
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public String Get_Token(string id, string logintype)
        {
            MemberClass mc = new MemberClass();
            String GUID = string.Empty;
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@logintype", logintype) };
            try
            {
                DataTable dT = new DataTable();
                mc.sSQL_Value = "SELECT TOKEN FROM LOGIN_RECORD with (nolock)  WHERE ACCOUNT_ID = @id AND ";
                mc.sSQL_Value = mc.sSQL_Value + " LOGIN_TYPE = @logintype ORDER BY EXPIRE_TIME DESC ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    GUID = dT.Rows[0].ItemArray[0].ToString().Trim().ToUpper();
                    mc.strRt_Value = GUID;
                }
                else { mc.strRt_Value = ""; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return mc.strRt_Value;
        }

        public bool IsValidEmail(string strIn)
        {
            // Return true if strIn is in valid e-mail format.
            return Regex.IsMatch(strIn,
                         @"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$");
        }

        /// <summary>
        /// 檢查TOKEN及有效期限
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="token">TOKEN</param>
        /// <returns></returns>
        public bool Check_Token(string id, string logintype, string token)
        {
            MemberClass mc = new MemberClass();
            bool bResult = false;
            DateTime EXPIRE_TIME = new DateTime();
            String GUID = string.Empty;
            String sToken = string.Empty;
            sToken = token.ToUpper().Trim();
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@logintype", logintype) };
            try
            {
                DataTable dT = new DataTable();
                mc.sSQL_Value = "SELECT EXPIRE_TIME,TOKEN FROM LOGIN_RECORD with (nolock)  WHERE ACCOUNT_ID = @id AND ";
                mc.sSQL_Value = mc.sSQL_Value + " LOGIN_TYPE = @logintype ORDER BY EXPIRE_TIME DESC ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    EXPIRE_TIME = Convert.ToDateTime(dT.Rows[0].ItemArray[0]);
                    GUID = dT.Rows[0].ItemArray[1].ToString().Trim().ToUpper();
                    if ((sToken.Equals(GUID)) && (DateTime.Now < EXPIRE_TIME)) { bResult = true; }
                    else { bResult = false; }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return bResult;
        }

        /// <summary>
        /// 比對會員密碼是否正確
        /// </summary>
        /// <param name="oldPwd">舊密碼</param>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public bool Compare_oldPwd(string oldPwd, string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            bool bResult = false;
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@oldPwd", oldPwd),
                new SqlParameter("@id", id)
            };
            try
            {
                mc.sSQL_Value = "SELECT * FROM ACCOUNTS WHERE ID = @id AND PASSWORD = @oldPwd ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                { bResult = true; }
                else
                { bResult = false; }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return bResult;
        }

        public String Get_CNAME(string verifyvalue)
        {
            String sSQL = String.Empty;
            String strRt = String.Empty;
            String sCNAME = String.Empty;
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@verifyvalue", verifyvalue) 
            };
            try
            {
                if (IsValidEmail(verifyvalue))
                    sSQL = "select CNAME from accounts with (nolock)  where email = '" + verifyvalue + "'";
                else
                    sSQL = "select CNAME from accounts with (nolock)  where mobile_no = '" + verifyvalue + "'";
                
                dT = oDB.ExecuteDT(sSQL);
                if (dT.Rows.Count > 0)
                {
                    sCNAME = dT.Rows[0].ItemArray[0].ToString();
                    strRt = sCNAME;
                }else
                    strRt = "0";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return strRt;
        }

        public String Get_Email(string accountid)
        {
            String sSQL = String.Empty;
            String strRt = String.Empty;
            String sEmail = String.Empty;
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@accountid", accountid) 
            };
            try
            {
                sSQL = "select email from accounts with (nolock)  where id = '" + accountid + "'";
                

                dT = oDB.ExecuteDT(sSQL);
                if (dT.Rows.Count > 0)
                {
                    sEmail = dT.Rows[0].ItemArray[0].ToString();
                    strRt = sEmail;
                }
                else
                    strRt = "0";
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return strRt;
        }

        /// <summary>
        /// 取得會員ID
        /// </summary>
        /// <param name="VerifyType"></param>
        /// <param name="VerifyValue"></param>
        /// <returns></returns>
        public String Get_ACCOUNTID(int VerifyType, String VerifyValue)
        {
            String sSQL = String.Empty;
            String strRt = String.Empty;
            String sID = String.Empty;
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@verifytype", VerifyType), 
                new SqlParameter("@verifyvalue", VerifyValue) 
            };
            try
            {
                if (VerifyType == 0) { sSQL = "select ID from accounts with (nolock)  where email = '" + VerifyValue + "'"; }
                else { sSQL = "select ID from accounts with (nolock)  where mobile_no = '" + VerifyValue + "'"; }

                dT = oDB.ExecuteDT(sSQL);
                if (dT.Rows.Count > 0)
                {
                    sID = dT.Rows[0].ItemArray[0].ToString();
                }
                strRt = sID;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return strRt;
        }

        /// <summary>
        /// 檢查電郵或手机资料是否已使用(false:资料已使用/true:未使用)
        /// </summary>
        /// <param name="VerifyType">認證方式</param>
        /// <param name="VerifyValue">輸入手机或電郵</param>
        /// <returns></returns>
        public bool IS_INUSE(int VerifyType, String VerifyValue, String id)
        {
            String sSQL = String.Empty;
            String sID = id;
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@verifyvalue", VerifyValue) 
            };
            try
            {
                if (VerifyType == 0) { sSQL = " select count(*) from accounts with (nolock)  where ID != '" + sID + "' and email = @verifyvalue and email is not null and email <> '' "; }
                else { sSQL = "select count(*) from accounts with (nolock)  where ID != '" + sID + "' and mobile_no = @verifyvalue and mobile_no is not null and mobile_no <> ''"; }

                dT = oDB.ExecuteDT(sSQL, parameters);
                if (Convert.ToInt16(dT.Rows[0].ItemArray[0]) > 0)
                {
                    return false;
                }
                else { return true; }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// 取得默認地址ID
        /// </summary>
        /// <param name="id"></param>
        /// <param name="usrip"></param>
        /// <returns></returns>
        public string Get_DefaultAddressID(string id, string usrip)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT BILLING_ADDRESS_ID FROM ACCOUNTS  with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0) { mc.sDefaultAddressID_Value = dT.Rows[0].ItemArray[0].ToString(); }
                mc.strRt_Value = mc.sDefaultAddressID_Value;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        /// <summary>
        /// 設定常用地址ID
        /// </summary>
        /// <param name="id"></param>
        /// <param name="default_addrid"></param>
        /// <returns></returns>
        public string Set_DefaultAddressID(string id, string default_addrid)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@addrid", default_addrid) };
            try
            {
                mc.sSQL_Value = "UPDATE ACCOUNTS SET BILLING_ADDRESS_ID = @addrid ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE ID = @id";
                mc.sResult_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                if (mc.sResult_Value != "0") { throw new Exception(mc.strRt_Value); }
                return "0";
            }
            catch (Exception ex) { return ex.ToString(); }
        }

        /// <summary>
        /// 比對新舊资料是否一致
        /// </summary>
        /// <param name="sID">會員ID</param>
        /// <param name="iVerifytype">認證方式(0:電郵/1:手机)</param>
        /// <param name="sVerifyValue">輸入手机或電郵</param>
        /// <returns>false:新舊资料一致,不需重新認證/true:需重新認證</returns>
        public String Is_Modify(string sID, int iVerifytype, string sVerifyValue)
        {

            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@id", sID),
                new SqlParameter("@verifytype", iVerifytype),
                new SqlParameter("@verifyvalue", sVerifyValue) 
            };
            try
            {
                if (iVerifytype == 0) { mc.sSQL_Value = "SELECT EMAIL FROM ACCOUNTS with (nolock)  WHERE ID = @id"; }
                else { mc.sSQL_Value = "SELECT MOBILE_NO FROM ACCOUNTS with (nolock)  WHERE ID = @id"; }
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    if (dT.Rows[0].ItemArray[0].Equals(sVerifyValue))
                    {
                        mc.strRt_Value = "false"; //新舊资料一致,不需重新認證
                    }
                    else { mc.strRt_Value = "true"; }
                }
                return mc.strRt_Value;
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
        }

        public String ACCOUNT_STATUS_CHECK(string id)
        {
            MemberClass mc = new MemberClass();
            clsLog oLog = new clsLog();
            DataTable dt = new DataTable();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@id", id)
            };
            try
            {
                mc.sSQL_Value = "select STATUS ";
                mc.sSQL_Value = mc.sSQL_Value + "FROM ACCOUNTS  with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE ID = @id ";
                oLog.writeLine("重送验证码", "", "", "ACCOUNT_STATUS_CHECK", APUtil.clsLog.enumMsgType.General, "SQL:" + mc.sSQL_Value + ",ID:" + id);
                dt = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dt.Rows.Count > 0)
                {
                    mc.strRt_Value = dt.Rows[0].ItemArray[0].ToString();
                }
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        public String EXPIRETIME_CHECK(string id)
        {
            MemberClass mc = new MemberClass();
            clsLog oLog = new clsLog();
            DataTable dt = new DataTable();
            DateTime dtNow = DateTime.Now;
            SqlParameter[] parameters = 
            {
                new SqlParameter("@id", id)
            };
            try
            {
                mc.sSQL_Value = "select ACCOUNT_ID,VERIFY_CODE,EXPIRE_TIME,RESEND_VERIFYCODE ";
                mc.sSQL_Value = mc.sSQL_Value + "FROM ACCOUNT_VERIFY with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE ACCOUNT_ID = @id ";
                oLog.writeLine("重送验证码", "", "", "ACCOUNT_STATUS_CHECK", APUtil.clsLog.enumMsgType.General, "SQL:" + mc.sSQL_Value + ",ID:" + id);
                dt = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dt.Rows.Count > 0)
                {
                    int data = 0;
                    if (DBNull.Value.Equals(dt.Rows[0]["RESEND_VERIFYCODE"]))
                    { data = 0; }
                    else
                    {
                        data = (int)dt.Rows[0]["RESEND_VERIFYCODE"];
                    }
                    //1.檢查是否在激活時限內
                    if (DateTime.Now > (DateTime)dt.Rows[0]["EXPIRE_TIME"])
                    { mc.strRt_Value = "0"; } //'已逾激活時限,可重新申請驗證碼,不需確認次數
                    else if ((int)data > 1)
                    {
                        mc.strRt_Value = "2";   //已逾重發次數
                    }
                    else
                    {
                        mc.strRt_Value = "1," + dt.Rows[0]["VERIFY_CODE"].ToString().Trim();   //未超過重發次數
                    }
                }
                //         If dt.Rows.Count > 0 Then
                //    '1.檢查是否在激活時限內
                //    If (dtNow > dt.Rows(0).Item("EXPIRE_TIME")) Then
                //        strRt = "0" '已逾激活時限,可重新申請驗證碼,不需確認次數
                //    ElseIf (dt.Rows(0).Item("RESEND_VERIFYCODE").ToString() > 1) Then
                //        strRt = "2" '已逾重發次數
                //    Else
                //        strRt = "1" & "," & dt.Rows(0).Item("VERIFY_CODE").ToString().Trim() '未超過重發次數
                //    End If
                //End If
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        /// <summary>
        /// 取得認證方式(0:電郵/1:手机)
        /// </summary>
        /// <param name="sID">會員ID</param>
        /// <returns>(0:電郵/1:手机)</returns>
        public String Get_VerifyType(string sID)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", sID) };
            try
            {
                mc.sSQL_Value = "SELECT VERIFY_TYPE FROM ACCOUNTS with (nolock)  WHERE ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    mc.strRt_Value = dT.Rows[0].ItemArray[0].ToString();
                }
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        /// <summary>
        /// 編輯會員基本资料
        /// </summary>
        /// <param name="sID">會員ID</param>
        /// <param name="sCNAME">姓名</param>
        /// <param name="sEMAIL">電郵</param>
        /// <param name="sMOBILE_NO">手机</param>
        /// <param name="sSEX">性別</param>
        /// <param name="sBirthday">生日</param>
        /// <param name="mailBody">認證信內容</param>
        /// <param name="subject">認證信主旨</param>
        ///  <param name="subject">SMS內容</param>
        /// <param name="checkcode">認證碼</param>
        /// <returns></returns>
        public String Set_Accounts(string sID, string sCNAME, string sEMAIL, string sMOBILE_NO, string sSEX, string sBirthday, string usrip, string mailBody = "mailbody", string subject = "subject", string checkcode = "checkcode", string smsbody = "smsbody")
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DateTime dt1 = new DateTime();
            String sNeedVerify = string.Empty;
            VerifyCode verifycode = new VerifyCode();
            oSend = new SendVerifyCode();
            bool is_inuse_mail = true;
            bool is_inuse_mobile = true;
            try
            {
                //取得認證方式
                String s1 = Get_VerifyType(sID);
                //檢查是否需要重新認證
                if (s1 == "0") { sNeedVerify = Is_Modify(sID, Convert.ToInt16(s1), sEMAIL); }
                else { sNeedVerify = Is_Modify(sID, Convert.ToInt16(s1), sMOBILE_NO); }

                //檢查是否重複
                if (sEMAIL != null && sEMAIL != "") { is_inuse_mail = IS_INUSE(0, sEMAIL, sID); }
                if (sMOBILE_NO != null && sMOBILE_NO != "") { is_inuse_mobile = IS_INUSE(1, sMOBILE_NO, sID); }
                if (is_inuse_mail == false || is_inuse_mobile == false)
                {
                    mc.sResult_Value = "1"; //電郵或手机已存在,不可變更
                    return mc.sResult_Value;
                }

                if (sNeedVerify == "true")   //需認證
                {
                    //UPDATE ACCOUNTS STATUS = 0
                    dt1 = Convert.ToDateTime(sBirthday);
                    SqlParameter[] parameters =
                    { 
                        new SqlParameter("@id", sID ),
                        new SqlParameter("@cname", sCNAME),
                        new SqlParameter("@email", sEMAIL),
                        new SqlParameter("@mobileno", sMOBILE_NO),
                        new SqlParameter("@sex", sSEX),
                        new SqlParameter("@birthday", dt1),
                        new SqlParameter("@UPDATE_IP", usrip)
                    };
                    mc.sSQL_Value = "UPDATE ACCOUNTS SET CNAME = @cname , EMAIL = @email, MOBILE_NO = @mobileno , SEX = @sex , BIRTHDAY = @birthday , UPDATE_IP = @UPDATE_IP , UPDATE_TIME = GETDATE() , STATUS = '0'";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ID = @id";
                    mc.sResult_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.sResult_Value != "0") { throw new Exception(mc.strRt_Value); }
                    //發送認證信
                    if (s1 == "0")
                    {
                        //發電郵
                        oSend.sendMail(sEMAIL, checkcode, mailBody, subject);
                    }
                    else
                    {
                        //發SMS
                    }
                }
                else   //不需認證,只更新资料
                {
                    dt1 = Convert.ToDateTime(sBirthday);
                    SqlParameter[] parameters =
                    { 
                        new SqlParameter("@id", sID ),
                        new SqlParameter("@cname", sCNAME),
                        new SqlParameter("@email", sEMAIL),
                        new SqlParameter("@mobileno", sMOBILE_NO),
                        new SqlParameter("@sex", sSEX),
                        new SqlParameter("@birthday", dt1),
                        new SqlParameter("@UPDATE_IP", usrip)
                    };
                    mc.sSQL_Value = "UPDATE ACCOUNTS SET CNAME = @cname , EMAIL = @email, MOBILE_NO = @mobileno , SEX = @sex , BIRTHDAY = @birthday, UPDATE_IP = @UPDATE_IP , UPDATE_TIME = GETDATE()";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ID = @id";
                    mc.sResult_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.sResult_Value != "0") { throw new Exception(mc.strRt_Value); }
                }

                //UPDATE ACCOUNT_VERIFY NEW VERIFY_CODE AND RESEND_VERIFYCODE = 0
                //String sCheckCode = verifycode.CheckCode(6);
                SqlParameter[] param1 = { new SqlParameter("@id", sID), new SqlParameter("@checkcode", checkcode) };
                mc.sSQL_Value = string.Empty;
                mc.sSQL_Value = "UPDATE ACCOUNT_VERIFY SET VERIFY_CODE = @checkcode , RESEND_VERIFYCODE = '0'";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE ACCOUNT_ID = @id";
                mc.sResult_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param1);
                if (mc.sResult_Value != "0") { throw new Exception(mc.strRt_Value); }
                return mc.sResult_Value;
            }
            catch (Exception ex) { return ex.ToString(); }
        }


        /// <summary>
        /// 新增/修改/刪除常用地址
        /// </summary>
        /// <param name="action">ADD/MODIFY/DELETE (新增/修改/刪除)</param>
        /// <param name="account_id">會員ID</param>
        /// <param name="province">省</param>
        /// <param name="city">市</param>
        /// <param name="zipcode">邮递区号</param>
        /// <param name="addr">地址</param>
        /// <param name="telno">家用电话</param>
        /// <param name="mobileno">手机</param>
        /// <param name="usrip">IP位址</param>
        /// <param name="is_default">是否為默認地址(刪除是也要給true or false)</param>
        /// <param name="id">地址ID (選擇性參數)</param>
        /// <returns></returns>
        public String Set_Addresses(string action, string account_id, string province, string city, string zipcode, string addr, string telno, string mobileno, string usrip, bool is_default = false, string id = "0")
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                if (action == "ADD")
                {
                    SqlParameter[] parameters = 
                    {
                        new SqlParameter("@account_id", account_id), 
                        new SqlParameter("@province", province),
                        new SqlParameter("@city", city),
                        new SqlParameter("@zipcode", zipcode),
                        new SqlParameter("@addr", addr),
                        new SqlParameter("@tel_no", telno),
                        new SqlParameter("@mobile_no", mobileno)
                    };
                    mc.sSQL_Value = "INSERT INTO ADDRESSES";
                    mc.sSQL_Value = mc.sSQL_Value + "(ACCOUNT_ID,PROVINCE,CITY,ZIPCODE,ADDR,TEL_NO,MOBILE_NO,CREATE_TIME )";
                    mc.sSQL_Value = mc.sSQL_Value + " VALUES (@account_id,@province,@city,@zipcode,@addr,@tel_no,@mobile_no,getdate())";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    mc.sLastAddrID_Value = string.Empty;
                    mc.sSQL_Value = string.Empty;
                    //如果新增的地址為默認地址,需設定ID為默認ADDRESSID
                    if (is_default)
                    {
                        mc.strRt_Value = Set_DefaultAddressID(account_id, mc.sLastAddrID_Value = Get_LastAddrID(account_id));
                    }
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    mc.strRt_Value = "0";
                }
                if (action == "MODIFY")
                {
                    if (id == "") { return "1"; } //1:ADDRESSID不可為空值
                    SqlParameter[] parameters = 
                    {
                        new SqlParameter("@id", id), 
                        new SqlParameter("@province", province),
                        new SqlParameter("@city", city),
                        new SqlParameter("@zipcode", zipcode),
                        new SqlParameter("@addr", addr),
                        new SqlParameter("@tel_no", telno),
                        new SqlParameter("@mobile_no", mobileno)
                    };
                    mc.sSQL_Value = "UPDATE ADDRESSES SET PROVINCE = @province , CITY = @city , ZIPCODE = @zipcode ,";
                    mc.sSQL_Value = mc.sSQL_Value + " ADDR = @addr , TEL_NO = @tel_no , MOBILE_NO = @mobile_no ,";
                    mc.sSQL_Value = mc.sSQL_Value + " UPDATE_TIME = GETDATE()";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ID = @id";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    mc.sLastAddrID_Value = string.Empty;
                    mc.sSQL_Value = string.Empty;
                    //如果新增的地址為默認地址,需設定ID為默認ADDRESSID
                    if (is_default)
                    {
                        mc.strRt_Value = Set_DefaultAddressID(account_id, id);
                    }
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    mc.strRt_Value = "0";
                }
                if (action == "DELETE")
                {
                    SqlParameter[] parameters = 
                    {
                        new SqlParameter("@id", id), 
                        new SqlParameter("@account_id", account_id)
                    };
                    if (Get_DefaultAddressID(account_id, usrip).Equals(id))
                    {
                        mc.strRt_Value = "2";
                        return mc.strRt_Value;
                    }
                    mc.sSQL_Value = "DELETE FROM ADDRESSES WHERE ID = @id";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    mc.strRt_Value = "0";
                }

                return mc.strRt_Value;
            }
            catch (Exception ex) { return ex.ToString(); }
        }

        public bool Check_Addresses(string account_id, string province, string city, string zipcode, string addr)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            bool bResult = false;
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@account_id", account_id),
                new SqlParameter("@province", province),
                new SqlParameter("@city", city),
                new SqlParameter("@zipcode", zipcode),
                new SqlParameter("@addr", addr)
            };
            try
            {
                mc.sSQL_Value = "SELECT PROVINCE,CITY,ZIPCODE,ADDR FROM ADDRESSES with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @account_id AND PROVINCE = @province ";
                mc.sSQL_Value = mc.sSQL_Value + " AND CITY = @city AND ZIPCODE = @zipcode AND ADDR = @addr ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count == 0) { bResult = true; }
                else { bResult = false; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return bResult;
        }

        /// <summary>
        /// 取得常用地址列表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="usrip"></param>
        /// <returns>DataTable</returns>
        public DataTable Get_Addresses(string id, string usrip)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                //mc.sSQL_Value = "SELECT ID,PROVINCE,CITY,ZIPCODE,ADDR,TEL_NO,MOBILE_NO FROM ADDRESSES";
                //mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                mc.sSQL_Value = "SELECT A.BILLING_ADDRESS_ID, B.ID, B.ACCOUNT_ID, B.PROVINCE, B.CITY, ";
                mc.sSQL_Value = mc.sSQL_Value + " B.ZIPCODE, B.ADDR,B.TEL_NO,B.MOBILE_NO ";
                mc.sSQL_Value = mc.sSQL_Value + " FROM ACCOUNTS AS A with (nolock)  INNER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " ADDRESSES AS B with (nolock)  ON A.ID = B.ACCOUNT_ID ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE (B.ACCOUNT_ID = @id) ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                DataTable table = new DataTable();
                table.Columns.Add("is_Default", typeof(bool));
                table.Columns.Add("ID", typeof(string));
                table.Columns.Add("LOCATION", typeof(string));
                table.Columns.Add("ZIPCODE", typeof(string));
                table.Columns.Add("ADDR", typeof(string));
                table.Columns.Add("MOBILE_NO", typeof(string));
                table.Columns.Add("TEL_NO", typeof(string));
                //table.Columns.Add("ZIPCODE", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    if (dT.Rows[i]["BILLING_ADDRESS_ID"].Equals(dT.Rows[i]["ID"]))
                    {
                        table.Rows.Add(true, dT.Rows[i]["ID"], dT.Rows[i]["PROVINCE"].ToString() +
                        dT.Rows[i]["CITY"].ToString(), dT.Rows[i]["ZIPCODE"], dT.Rows[i]["ADDR"],
                        dT.Rows[i]["MOBILE_NO"],dT.Rows[i]["TEL_NO"]);
                        //table.Rows.Add(true, dT.Rows[i]["ID"], dT.Rows[i]["PROVINCE"],
                        //dT.Rows[i]["CITY"], dT.Rows[i]["ADDR"],
                        //dT.Rows[i]["TEL_NO"], dT.Rows[i]["ZIPCODE"]);
                    }
                    else
                    {
                        table.Rows.Add(false, dT.Rows[i]["ID"], dT.Rows[i]["PROVINCE"].ToString() +
                        dT.Rows[i]["CITY"].ToString(), dT.Rows[i]["ZIPCODE"], dT.Rows[i]["ADDR"],
                        dT.Rows[i]["MOBILE_NO"], dT.Rows[i]["TEL_NO"]);
                        //table.Rows.Add(false, dT.Rows[i]["ID"], dT.Rows[i]["PROVINCE"],
                        //dT.Rows[i]["CITY"], dT.Rows[i]["ADDR"],
                        //dT.Rows[i]["TEL_NO"], dT.Rows[i]["ZIPCODE"]);
                    }
                }
                return table;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

        /// <summary>
        /// 取得最新一筆ADDRESSID
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public String Get_LastAddrID(string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT TOP (1) ID FROM ADDRESSES with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                mc.sSQL_Value = mc.sSQL_Value + " ORDER BY  CREATE_TIME DESC";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0) { mc.sLastAddrID_Value = dT.Rows[0].ItemArray[0].ToString(); }
                mc.strRt_Value = mc.sLastAddrID_Value;
                return mc.strRt_Value;
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }

        #endregion

        #region 出倉記錄


        #region 删除DataTable重复列，类似distinct
        /// <summary>   
        /// 删除DataTable重复列，类似distinct   
        /// </summary>   
        /// <param name="dt">DataTable</param>   
        /// <param name="Field">字段名</param>   
        /// <returns></returns>   
        public static DataTable DeleteSameRow(DataTable dt, string Field)
        {
            ArrayList indexList = new ArrayList();
            // 找出待删除的行索引   
            for (int i = 0; i < dt.Rows.Count - 1; i++)
            {
                if (!IsContain(indexList, i))
                {
                    for (int j = i + 1; j < dt.Rows.Count; j++)
                    {
                        if (dt.Rows[i][Field].ToString() == dt.Rows[j][Field].ToString())
                        {
                            indexList.Add(j);
                        }
                    }
                }
            }
            // 根据待删除索引列表删除行   
            for (int i = indexList.Count - 1; i >= 0; i--)
            {
                int index = Convert.ToInt32(indexList[i]);
                dt.Rows.RemoveAt(index);
            }
            return dt;
        }

        /// <summary>   
        /// 判断数组中是否存在   
        /// </summary>   
        /// <param name="indexList">数组</param>   
        /// <param name="index">索引</param>   
        /// <returns></returns>   
        public static bool IsContain(ArrayList indexList, int index)
        {
            for (int i = 0; i < indexList.Count; i++)
            {
                int tempIndex = Convert.ToInt32(indexList[i]);
                if (tempIndex == index)
                {
                    return true;
                }
            }
            return false;
        }
        #endregion

        /// <summary>
        /// 取得箱子的照片路徑
        /// </summary>
        /// <param name="boxid"></param>
        /// <returns></returns>
        public string Get_BOXIMAGEPATH(string boxid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@boxid", boxid) };
            String sDefault_IMAGEPATH = System.Configuration.ConfigurationSettings.AppSettings["DEFAULT_IMAGE_PATH"];
            try
            {
                mc.sSQL_Value = "SELECT TOP (1) IMAGE_PATH FROM BOX_CONTENTS with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_BOX_ID = @boxid";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0) { mc.strRt_Value = dT.Rows[0].ItemArray[0].ToString(); }
                else { mc.strRt_Value = sDefault_IMAGEPATH; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return mc.strRt_Value;
        }


        public DataTable Get_PickupData(string id, string startdate, string enddate, string keyword)
        {
            MemberClass mc = new MemberClass();
            int statusid = 100;
            DataTable dT = new DataTable();
            try
            {
                SqlParameter[] param1 = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };

                mc.sSQL_Value = "SELECT   A.BOX_NO, CONVERT(VARCHAR(10), B.UNLINK_TIME, 23) AS UNLINK_TIME, ";
                mc.sSQL_Value = mc.sSQL_Value + " A.BOX_DESC ";
                mc.sSQL_Value = mc.sSQL_Value + " FROM BOXES AS A with (nolock)  LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_NO = B.BOX_ID LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " (SELECT DISTINCT ACCOUNT_BOX_ID FROM BOX_CONTENTS  with (nolock)) AS C ON A.BOX_NO = C.ACCOUNT_BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE (B.ACCOUNT_ID = @id) AND (A.STATUS_ID = @statusid) ";

                if (keyword != "" && (startdate == "" && enddate == ""))
                {
                    mc.sSQL_Value = mc.sSQL_Value + " AND (A.BOX_DESC LIKE '%" + keyword + "%')";
                }
                else if ((startdate != "" && enddate != "") && keyword == "")
                {
                    mc.sSQL_Value = mc.sSQL_Value + "AND (B.UNLINK_TIME BETWEEN ";
                    mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                }
                else if (keyword != "" && startdate != "" && enddate != "")
                {
                    mc.sSQL_Value = mc.sSQL_Value + "AND (B.UNLINK_TIME BETWEEN ";
                    mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59') ";
                    mc.sSQL_Value = mc.sSQL_Value + "AND (A.BOX_DESC LIKE '%" + keyword + "%') ";
                }
                dT = oDB.ExecuteDT(mc.sSQL_Value, param1);
                if (dT.Rows.Count <= 0 || dT == null) { dT = null; return dT; }
                DataTable table = new DataTable();
                table.Columns.Add("BOX_NO", typeof(string));
                table.Columns.Add("UNLINK_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("IMAGE_PATH", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    String s1 = string.Empty;
                    s1 = dT.Rows[i]["BOX_NO"].ToString();
                    String s2 = string.Empty;
                    s2 = Get_BOXIMAGEPATH(s1);
                    table.Rows.Add(dT.Rows[i]["BOX_NO"], dT.Rows[i]["UNLINK_TIME"], dT.Rows[i]["BOX_DESC"], s2);
                }
                return table;

            }
            catch (Exception) { throw new Exception(mc.strRt_Value); }
        }

        /// <summary>
        /// 取得箱內资料
        /// </summary>
        /// <param name="boxid">箱號</param>
        /// <returns></returns>
        public DataTable Get_BOXCONTENT(string boxid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@boxid", boxid) };
            try
            {
                mc.sSQL_Value = "SELECT ID,ITEM_TYPE,ITEM_DESC,IMAGE_PATH FROM BOX_CONTENTS with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_BOX_ID = @boxid";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        /// <summary>
        /// 取得出倉記錄(以日期區間搜尋)
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="startdate">起始日期(格式:2014/07/31)</param>
        /// <param name="enddate">結束日期(格式:2014/07/31)</param>
        /// <returns></returns>
        public DataTable Get_PickupData(string id, string startdate, string enddate)
        {
            MemberClass mc = new MemberClass();
            int statusid = 100;
            DataTable dT = new DataTable();
            try
            {
                SqlParameter[] param1 = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };

                mc.sSQL_Value = "SELECT   A.BOX_NO, CONVERT(VARCHAR(10), B.UNLINK_TIME, 23) AS UNLINK_TIME, ";
                mc.sSQL_Value = mc.sSQL_Value + " A.BOX_DESC ";
                mc.sSQL_Value = mc.sSQL_Value + " FROM BOXES AS A with (nolock)  LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_NO = B.BOX_ID LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " (SELECT DISTINCT ACCOUNT_BOX_ID FROM BOX_CONTENTS  with (nolock)) AS C ON A.BOX_NO = C.ACCOUNT_BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE (B.ACCOUNT_ID = @id) AND (A.STATUS_ID = @statusid) ";
                if (startdate != "" && enddate != "")
                {
                    mc.sSQL_Value = mc.sSQL_Value + "AND (B.UNLINK_TIME BETWEEN ";
                    mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                }

                dT = oDB.ExecuteDT(mc.sSQL_Value, param1);
                if (dT.Rows.Count <= 0 || dT == null) { dT = null; return dT; }
                DataTable table = new DataTable();
                table.Columns.Add("BOX_NO", typeof(string));
                table.Columns.Add("UNLINK_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("IMAGE_PATH", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    String s1 = string.Empty;
                    s1 = dT.Rows[i]["BOX_NO"].ToString();
                    String s2 = string.Empty;
                    s2 = Get_BOXIMAGEPATH(s1);
                    table.Rows.Add(dT.Rows[i]["BOX_NO"], dT.Rows[i]["UNLINK_TIME"], dT.Rows[i]["BOX_DESC"], s2);
                }
                return table;

            }
            catch (Exception) { throw new Exception(mc.strRt_Value); }
        }
        /// <summary>
        /// 取得出倉記錄(以描述关键字搜尋)
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable Get_PickupData(string id, string keyword)
        {
            MemberClass mc = new MemberClass();
            int statusid = 100;
            DataTable dT = new DataTable();
            try
            {
                SqlParameter[] param1 = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };

                mc.sSQL_Value = "SELECT   A.BOX_NO, CONVERT(VARCHAR(10), B.UNLINK_TIME, 23) AS UNLINK_TIME, ";
                mc.sSQL_Value = mc.sSQL_Value + " A.BOX_DESC ";
                mc.sSQL_Value = mc.sSQL_Value + " FROM BOXES AS A with (nolock)  LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_NO = B.BOX_ID LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " (SELECT DISTINCT ACCOUNT_BOX_ID FROM BOX_CONTENTS  with (nolock)) AS C ON A.BOX_NO = C.ACCOUNT_BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE (B.ACCOUNT_ID = @id) AND (A.STATUS_ID = @statusid) ";
                if (keyword != "")
                {
                    mc.sSQL_Value = mc.sSQL_Value + " AND (A.BOX_DESC LIKE '%" + keyword + "%')";
                }
                dT = oDB.ExecuteDT(mc.sSQL_Value, param1);
                if (dT.Rows.Count <= 0 || dT == null) { dT = null; return dT; }
                DataTable table = new DataTable();
                table.Columns.Add("BOX_NO", typeof(string));
                table.Columns.Add("UNLINK_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("IMAGE_PATH", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    String s1 = string.Empty;
                    s1 = dT.Rows[i]["BOX_NO"].ToString();
                    String s2 = string.Empty;
                    s2 = Get_BOXIMAGEPATH(s1);
                    table.Rows.Add(dT.Rows[i]["BOX_NO"], dT.Rows[i]["UNLINK_TIME"], dT.Rows[i]["BOX_DESC"], s2);
                }
                return table;
            }
            catch (Exception) { throw new Exception(mc.strRt_Value); }
        }

        /// <summary>
        /// 取得出倉記錄
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public DataTable Get_PickupData(string id)
        {
            MemberClass mc = new MemberClass();
            int statusid = 100;
            DataTable dT = new DataTable();
            try
            {
                SqlParameter[] param1 = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };

                mc.sSQL_Value = "SELECT   A.BOX_NO, CONVERT(VARCHAR(10), B.UNLINK_TIME, 23) AS UNLINK_TIME, ";
                mc.sSQL_Value = mc.sSQL_Value + " A.BOX_DESC ";
                mc.sSQL_Value = mc.sSQL_Value + " FROM BOXES AS A with (nolock)  LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_NO = B.BOX_ID LEFT OUTER JOIN ";
                mc.sSQL_Value = mc.sSQL_Value + " (SELECT DISTINCT ACCOUNT_BOX_ID FROM BOX_CONTENTS  with (nolock)) AS C ON A.BOX_NO = C.ACCOUNT_BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE (B.ACCOUNT_ID = @id) AND (A.STATUS_ID = @statusid)";
                dT = oDB.ExecuteDT(mc.sSQL_Value, param1);
                if (dT.Rows.Count <= 0 || dT == null) { dT = null; return dT; }
                DataTable table = new DataTable();
                table.Columns.Add("BOX_NO", typeof(string));
                table.Columns.Add("UNLINK_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("IMAGE_PATH", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    String s1 = string.Empty;
                    s1 = dT.Rows[i]["BOX_NO"].ToString();
                    String s2 = string.Empty;
                    s2 = Get_BOXIMAGEPATH(s1);
                    table.Rows.Add(dT.Rows[i]["BOX_NO"], dT.Rows[i]["UNLINK_TIME"], dT.Rows[i]["BOX_DESC"], s2);
                }
                return table;

            }
            catch (Exception) { throw new Exception(mc.strRt_Value); }
        }

        #endregion

        #region 充值

        /// <summary>
        /// 付款成功後充值流程
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="AMOUNT_PAID"></param>
        /// <param name="orderid"></param>
        /// <param name="TXN_NO"></param>
        /// <param name="TXN_MSG"></param>
        /// <param name="logintype"></param>
        /// <param name="sToken"></param>
        /// <param name="USRIP"></param>
        /// <returns></returns>
        public String Direct_pay(string ID, double AMOUNT_PAID, string orderid, string TXN_NO, string TXN_MSG, string logintype, string sToken, string USRIP)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataSet ds = new DataSet();
            double dAccountBalance = 0;
            double dTempBanlance = 0;
            try
            {
                dAccountBalance = Get_AccountBalance(ID);   //取得帳戶餘額
                dTempBanlance = dAccountBalance + AMOUNT_PAID;      //計算餘額
                
                //將支付寶回傳資料寫入PAYMENT
                if (TXN_MSG == "TRADE_SUCCESS" || TXN_MSG == "TRADE_FINISHED")
                {
                    SqlParameter[] param1 = 
                    {
                        new SqlParameter("@account_id", ID), 
                        new SqlParameter("@COIN_AMOUNT", AMOUNT_PAID),
                        new SqlParameter("@TXNTYPE", "Payment"),
                        new SqlParameter("@ACCOUNT_BALANCE", dTempBanlance),
                        new SqlParameter("@ORDERID",orderid)
                    };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "INSERT INTO ACCOUNT_LEDGER ";
                    mc.sSQL_Value = mc.sSQL_Value + "(ACCOUNT_ID,COIN_AMOUNT,TXN_TIME,TXN_TYPE,LINE_ITEM,BOX_ID,WORKORDER_ID,ACCOUNT_BALANCE)";
                    mc.sSQL_Value = mc.sSQL_Value + " VALUES (@account_id,@COIN_AMOUNT,GETDATE(),@TXNTYPE,'','',@ORDERID,@ACCOUNT_BALANCE)";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param1);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    //計算完餘額,寫入ACCOUNT_BALANCE
                    SqlParameter[] param2 = { new SqlParameter("@account_id", ID), new SqlParameter("@balance", dTempBanlance) };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "UPDATE ACCOUNT_BALANCE SET ACCOUNT_BALANCE = @balance ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @account_id";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param2);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    SqlParameter[] param3 = { new SqlParameter("@orderid", orderid), new SqlParameter("@txnno", TXN_NO), new SqlParameter("@txnmsg", TXN_MSG), new SqlParameter("@AMOUNT_CREDITED", AMOUNT_PAID), };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "UPDATE PAYMENT SET TXN_NO = @txnno,TXN_MSG = @txnmsg,AMOUNT_CREDITED = @AMOUNT_CREDITED,RESPONSE_TIME = GETDATE() ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ORDERID = @orderid";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param3);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
                else
                {
                    SqlParameter[] param3 = { new SqlParameter("@orderid", orderid), new SqlParameter("@txnno", TXN_NO), new SqlParameter("@txnmsg", TXN_MSG), new SqlParameter("@AMOUNT_CREDITED", AMOUNT_PAID), };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "UPDATE PAYMENT SET TXN_NO = @txnno,TXN_MSG = @txnmsg,AMOUNT_CREDITED = @AMOUNT_CREDITED,RESPONSE_TIME = GETDATE() ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ORDERID = @orderid";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param3);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
                
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        public String Pre_Payment(string ID, string CREDIT_PACK_ID, double AMOUNT_PAID, string PAYMENT_TYPE_ID)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataSet ds = new DataSet();
            double dAccountBalance = 0;
            String Env = System.Configuration.ConfigurationSettings.AppSettings["Environment"];
            String orderid = string.Empty;
            try
            {
                if(Env == "TEST")
                    orderid = Get_SEQNO("DEP", "DEP");
                else
                    orderid = Get_SEQNO("DEPO", "DEPO");

                dAccountBalance = Get_AccountBalance(ID);   //取得帳戶餘額
                SqlParameter[] parameters = 
                    {
                        new SqlParameter("@account_id", ID), 
                        new SqlParameter("@Creditpackid", CREDIT_PACK_ID),
                        new SqlParameter("@amountpaid", AMOUNT_PAID),
                        new SqlParameter("@paymenttypeid", PAYMENT_TYPE_ID),
                        new SqlParameter("@orderid", orderid)
                    };
                mc.sSQL_Value = "INSERT INTO PAYMENT ";
                mc.sSQL_Value = mc.sSQL_Value + "(ACCOUNT_ID,CREDIT_PACK_ID,AMOUNT_PAID,AMOUNT_CREDITED,PAYMENT_TYPE_ID,TXN_NO,PAYMENT_TIME,CONFIRM_FLAG,CONFIRM_DATE,ORDERID )";
                mc.sSQL_Value = mc.sSQL_Value + " VALUES (@account_id , @Creditpackid , @amountpaid , 0 , @paymenttypeid , '' , GETDATE() , 'N' , '' , @orderid)";
                mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                if (mc.strRt_Value != "0")
                {
                    throw new Exception(mc.strRt_Value);
                }
                else
                {
                    mc.strRt_Value = orderid;
                }
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }
            return mc.strRt_Value;
        }

        public String Payment(string ID, string CREDIT_PACK_ID, double AMOUNT_PAID, string PAYMENT_TYPE_ID, string TXN_NO, string CONFIRM_FLAG)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataSet ds = new DataSet();
            double dAccountBalance = 0;
            double dTempBanlance = 0;
            try
            {
                //取得订单编号
                //SqlParameter[] param3 = 
                //{
                //     new SqlParameter("@TYPE","儲值"),
                //     new SqlParameter("@PREFIX","儲值"),
                //     new SqlParameter("@NO_LENGTH",10)
                //};
                //Get_SEQNO("儲值", "儲值");
                //ds = oDB.ExecuteSPGetDT("USP_GET_SYS_SEQ_NO", param3);  //取得订单编号
                //String orderid = ds.Tables[0].Rows[0].ItemArray[0].ToString();
                String orderid = Get_SEQNO("DEP", "DEP");
                dAccountBalance = Get_AccountBalance(ID);   //取得帳戶餘額
                SqlParameter[] parameters = 
                    {
                        new SqlParameter("@account_id", ID), 
                        new SqlParameter("@Creditpackid", CREDIT_PACK_ID),
                        new SqlParameter("@amountpaid", AMOUNT_PAID),
                        new SqlParameter("@paymenttypeid", PAYMENT_TYPE_ID),
                        new SqlParameter("@txnno", TXN_NO),
                        new SqlParameter("@confirm_flag", CONFIRM_FLAG)
                    };
                mc.sSQL_Value = "INSERT INTO PAYMENT ";
                mc.sSQL_Value = mc.sSQL_Value + "(ACCOUNT_ID,CREDIT_PACK_ID,AMOUNT_PAID,AMOUNT_CREDITED,PAYMENT_TYPE_ID,TXN_NO,PAYMENT_TIME,CONFIRM_FLAG,CONFIRM_DATE )";
                mc.sSQL_Value = mc.sSQL_Value + " VALUES (@account_id,@Creditpackid,@amountpaid,@amountpaid,@paymenttypeid,@txnno,GETDATE(),'N',GETDATE())";
                mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                if (mc.strRt_Value != "0")
                {
                    throw new Exception(mc.strRt_Value);
                }

                if (CONFIRM_FLAG == "Y")
                {
                    //儲值已確認,资料寫入ACCOUNT_LEDGER
                    dTempBanlance = dAccountBalance + AMOUNT_PAID;      //計算餘額
                    SqlParameter[] param1 = 
                    {
                        new SqlParameter("@account_id", ID), 
                        new SqlParameter("@COIN_AMOUNT", AMOUNT_PAID),
                        new SqlParameter("@TXNTYPE", "Payment"),
                        new SqlParameter("@ACCOUNT_BALANCE", dTempBanlance),
                        new SqlParameter("@ORDERID",orderid)
                    };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "INSERT INTO ACCOUNT_LEDGER ";
                    mc.sSQL_Value = mc.sSQL_Value + "(ACCOUNT_ID,COIN_AMOUNT,TXN_TIME,TXN_TYPE,LINE_ITEM,BOX_ID,WORKORDER_ID,ACCOUNT_BALANCE)";
                    mc.sSQL_Value = mc.sSQL_Value + " VALUES (@account_id,@COIN_AMOUNT,GETDATE(),@TXNTYPE,'','',@ORDERID,@ACCOUNT_BALANCE)";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param1);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    //計算完餘額,寫入ACCOUNT_BALANCE
                    SqlParameter[] param2 = { new SqlParameter("@account_id", ID), new SqlParameter("@balance", dTempBanlance) };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "UPDATE ACCOUNT_BALANCE SET ACCOUNT_BALANCE = @balance ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @account_id";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param2);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
                return mc.strRt_Value;
            }
            catch (Exception ex) { return ex.ToString(); }
        }

        /// <summary>
        /// 取得商品资料
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable Get_Product(string type)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@type", type)
            };
            try
            {
                if (type == "ALL")
                {
                    mc.sSQL_Value = "SELECT TYPE,PRODUCT_NAME,UNIT,PRICE  FROM PRODUCT with (nolock)  ";
                }
                else
                {
                    mc.sSQL_Value = "SELECT TYPE,PRODUCT_NAME,UNIT,PRICE  FROM PRODUCT with (nolock)  ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE TYPE = @type ";
                }
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }


        #region 交易記錄

        /// <summary>
        /// 取得订单明細
        /// </summary>
        /// <param name="orderid">订单编号</param>
        /// <returns></returns>
        public DataTable Get_OrderDetail(string orderid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@orderid", orderid) };
            try
            {
                mc.sSQL_Value = "SELECT  PRODUCT_NAME,QUANTITY_VERIFY, PRICE, ";
                mc.sSQL_Value = mc.sSQL_Value + " SUB_TOTAL_VERIFY, NOTE FROM ORDER_DETAIL with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDERID = @orderid";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        /// <summary>
        /// 取得交易記錄
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public DataTable Get_AccountLedger(string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT CONVERT(VARCHAR(10),TXN_TIME,23) AS TXN_TIME ,WORKORDER_ID,TXN_TYPE,COIN_AMOUNT,ACCOUNT_BALANCE FROM ACCOUNT_LEDGER with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }



        /// <summary>
        /// 取得帳戶餘額
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public double Get_AccountBalance(string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            double bBalance = 0.921;
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT ACCOUNT_BALANCE FROM ACCOUNT_BALANCE with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0) { bBalance = Convert.ToDouble(dT.Rows[0].ItemArray[0]); }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return bBalance;
        }

        /// <summary>
        /// 取得帳戶餘額
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public DataTable Get_AccountBalance_DT(string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT ACCOUNT_BALANCE FROM ACCOUNT_BALANCE with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);

                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }
    
        /// <summary>
        /// 初始化會員帳戶餘額
        /// </summary>
        /// <param name="accountid"></param>
        /// <returns></returns>
        public String init_AccBanalance(string accountid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] para = { new SqlParameter("@id", accountid) };
            try
            {
                //檢查ACCOUNT_BALANCE內是否已有資料
                mc.sSQL_Value = "SELECT ACCOUNT_BALANCE FROM ACCOUNT_BALANCE with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, para);

                if (dT.Rows.Count <= 0)
                {
                    //如果ACCOUNT_BALANCE內沒有資料,則寫入一筆
                    SqlParameter[] parameters = 
                    {
                        new SqlParameter("@accountid", accountid)
                    };
                    mc.sSQL_Value = "insert into ACCOUNT_BALANCE (ACCOUNT_ID,ACCOUNT_BALANCE,FREE_MONEY_BALANCE) ";
                    mc.sSQL_Value = mc.sSQL_Value + " VALUES (@accountid,0.00,0.00 )";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
                else
                {
                    //如果ACCOUNT_BALANCE內有資料,則將餘額初始化為0
                    double dTempBanlance = 0;
                    SqlParameter[] param2 = { new SqlParameter("@balance", dTempBanlance), new SqlParameter("@account_id", accountid) };
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "UPDATE ACCOUNT_BALANCE SET ACCOUNT_BALANCE = @balance ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @account_id";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param2);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
            }
            catch (Exception ex)
            {
                return ex.ToString();
            }

            return mc.strRt_Value;
        }

        #endregion

        #region 我的儲存



        /// <summary>
        /// 新增订单明細
        /// </summary>
        /// <param name="workorderid">订单编号</param>
        /// <param name="productname">產品名稱</param>
        /// <param name="qty">數量</param>
        /// <param name="price">单價</param>
        /// <param name="sub_total">小計</param>
        /// <param name="note">備註</param>
        /// <returns></returns>
        public String Add_Orderdetail(string workorderid, string productname, string qty, string price, string sub_total, string note)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = 
            {
                new SqlParameter("@workorderid", workorderid),
                new SqlParameter("@productname", productname),
                new SqlParameter("@qty", qty),
                new SqlParameter("@price", price),
                new SqlParameter("@sub_total", sub_total),
                new SqlParameter("@note", note)
            };
            mc.sSQL_Value = "INSERT INTO ORDER_DETAIL (WORKORDERID,PRODUCT_NAME,QUANTITY,PRICE,SUB_TOTAL,NOTE) ";
            mc.sSQL_Value = mc.sSQL_Value + " VALUES (@workorderid, @productname, ";
            mc.sSQL_Value = mc.sSQL_Value + " @qty, @price, @sub_total, @note)";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0")
            {
                throw new Exception(mc.strRt_Value);
            }
            return mc.strRt_Value;
        }

        /// <summary>
        /// 取得待出倉箱號清单(By关键字搜尋)
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable Get_Atwarehouse_Boxlist(string id, string keyword)
        {
            MemberClass mc = new MemberClass();
            String statusid = "60";
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            String sDefault_IMAGEPATH = System.Configuration.ConfigurationSettings.AppSettings["DEFAULT_IMAGE_PATH"];
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                //mc.sSQL_Value = "select A.BOX_ID,CONVERT(VARCHAR(10),C.UPDATE_TIME,111) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from WORKORDER_BOX AS A with (nolock)  ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_ID = B.BOX_ID ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN BOXES AS C with (nolock)  ON A.BOX_ID = C.BOX_NO";
                //mc.sSQL_Value = mc.sSQL_Value + " WHERE B.ACCOUNT_ID = @id AND C.STATUS_ID = @statusid AND A.BOX_DESC LIKE '%" + keyword + "%'";
                mc.sSQL_Value = "select A.BOX_NO AS BOX_ID,CONVERT(VARCHAR(10),B.LINK_TIME,23) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from BOXES as A with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)ON A.BOX_NO = B.BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE B.ACCOUNT_ID = @id AND A.STATUS_ID = @statusid AND A.BOX_DESC LIKE '%" + keyword + "%'";

                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                table.Columns.Add("BOX_ID", typeof(string));
                table.Columns.Add("UPDATE_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("BOX_FIRST_IMAGE", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    if (dT.Rows[i]["BOX_FIRST_IMAGE"].ToString() == "" || dT.Rows[i]["BOX_FIRST_IMAGE"] == null)
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], sDefault_IMAGEPATH);
                    }
                    else
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], dT.Rows[i]["BOX_FIRST_IMAGE"]);
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }

        /// <summary>
        /// 取得待出倉箱號清单(By日期區間搜尋)
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="startdate">起始日期</param>
        /// <param name="enddate">結束日期</param>
        /// <returns></returns>
        public DataTable Get_Atwarehouse_Boxlist(string id, string startdate, string enddate)
        {
            MemberClass mc = new MemberClass();
            String statusid = "60";
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            String sDefault_IMAGEPATH = System.Configuration.ConfigurationSettings.AppSettings["DEFAULT_IMAGE_PATH"];
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                //mc.sSQL_Value = "select A.BOX_ID,CONVERT(VARCHAR(10),C.UPDATE_TIME,111) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from WORKORDER_BOX AS A with (nolock)  ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_ID = B.BOX_ID ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN BOXES AS C with (nolock)  ON A.BOX_ID = C.BOX_NO";
                //mc.sSQL_Value = mc.sSQL_Value + " WHERE B.ACCOUNT_ID = @id AND C.STATUS_ID = @statusid AND UPDATE_TIME BETWEEN ";
                //mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59'";
                mc.sSQL_Value = "select A.BOX_NO AS BOX_ID,CONVERT(VARCHAR(10),B.LINK_TIME,23) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from BOXES as A with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)ON A.BOX_NO = B.BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE B.ACCOUNT_ID = @id AND A.STATUS_ID = @statusid AND B.LINK_TIME BETWEEN ";
                mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59'";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                table.Columns.Add("BOX_ID", typeof(string));
                table.Columns.Add("UPDATE_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("BOX_FIRST_IMAGE", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    if (dT.Rows[i]["BOX_FIRST_IMAGE"].ToString() == "" || dT.Rows[i]["BOX_FIRST_IMAGE"] == null)
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], sDefault_IMAGEPATH);
                    }
                    else
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], dT.Rows[i]["BOX_FIRST_IMAGE"]);
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }

        /// <summary>
        /// 取得待出倉箱號清单
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public DataTable Get_Atwarehouse_Boxlist(string id)
        {
            MemberClass mc = new MemberClass();
            String statusid = "60";
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            String sDefault_IMAGEPATH = System.Configuration.ConfigurationSettings.AppSettings["DEFAULT_IMAGE_PATH"];
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                //mc.sSQL_Value = "select A.BOX_ID,CONVERT(VARCHAR(10),C.UPDATE_TIME,111) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from WORKORDER_BOX AS A with (nolock)  ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)  ON A.BOX_ID = B.BOX_ID ";
                //mc.sSQL_Value = mc.sSQL_Value + " LEFT OUTER JOIN BOXES AS C with (nolock)  ON A.BOX_ID = C.BOX_NO";
                //mc.sSQL_Value = mc.sSQL_Value + " WHERE B.ACCOUNT_ID = @id AND C.STATUS_ID = @statusid";
                mc.sSQL_Value = "select A.BOX_NO AS BOX_ID,CONVERT(VARCHAR(10),B.LINK_TIME,23) AS UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from BOXES as A with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "LEFT OUTER JOIN ACCOUNT_BOXES AS B with (nolock)ON A.BOX_NO = B.BOX_ID ";
                mc.sSQL_Value = mc.sSQL_Value + "WHERE B.ACCOUNT_ID = @id AND A.STATUS_ID = @statusid";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                table.Columns.Add("BOX_ID", typeof(string));
                table.Columns.Add("UPDATE_TIME", typeof(string));
                table.Columns.Add("BOX_DESC", typeof(string));
                table.Columns.Add("BOX_FIRST_IMAGE", typeof(string));

                for (int i = 0; i < dT.Rows.Count; i++)
                {
                    if (dT.Rows[i]["BOX_FIRST_IMAGE"].ToString() == "" || dT.Rows[i]["BOX_FIRST_IMAGE"] == null)
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], sDefault_IMAGEPATH);
                    }
                    else
                    {
                        table.Rows.Add(dT.Rows[i]["BOX_ID"], dT.Rows[i]["UPDATE_TIME"], dT.Rows[i]["BOX_DESC"], dT.Rows[i]["BOX_FIRST_IMAGE"]);
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }

        /// <summary>
        /// 新增订单
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="workorderid">订单编号</param>
        /// <param name="workorder_type">類別(例如:入库(IN),出库(OUT))</param>
        /// <param name="expect_date">期望取箱/送箱日期</param>
        /// <param name="expect_date1">期望送空箱日期</param>
        /// <param name="province">州</param>
        /// <param name="city">市</param>
        /// <param name="addr">地址</param>
        /// <param name="zipcode">邮递区号</param>
        /// <param name="mobileno">手机</param>
        /// <param name="telno">家用电话</param>
        /// <param name="is_addaddr">是否加入常用地址</param>
        /// <param name="boxamount">箱數</param>
        /// <param name="usrip">IP位址</param>
        /// <returns></returns>
        public String Add_Workorder(string id, string workorderid, string workorder_type, string expect_date, string expect_date1, string province, string city,
            string addr, string zipcode, string mobileno, string telno, bool is_addaddr, string boxamount, string usrip)
        {
            MemberClass mc = new MemberClass();
            string statusid = string.Empty;
            if (workorder_type.Equals("IN")) { statusid = "0"; }
            else { statusid = "70"; }
            SqlParameter[] parameters = 
            {
                new SqlParameter("@workorderid", workorderid),
                new SqlParameter("@workorder_type", workorder_type),
                new SqlParameter("@expect_date", expect_date),      //期望取箱/送箱日期
                new SqlParameter("@expect_date1", expect_date1),    //期望送空箱日期
                new SqlParameter("@province", province),
                new SqlParameter("@city", city),
                new SqlParameter("@addr", addr),
                new SqlParameter("@zipcode", zipcode),
                new SqlParameter("@mobileno", mobileno),
                new SqlParameter("@telno", telno),
                new SqlParameter("@boxamount", boxamount),
                new SqlParameter("@statusid",statusid),
                new SqlParameter("@id", id)
            };
            try
            {
                mc.sSQL_Value = string.Empty;
                mc.sSQL_Value = "INSERT INTO WORKORDER (WORKORDER_ID,APPLY_TIME,WORKORDER_TYPE,ACCOUNT_ID, ";
                mc.sSQL_Value = mc.sSQL_Value + " PROVINCE,CITY,ZIPCODE,ADDR,BOX_AMOUNT,TEL_NO,MOBILE_NO,EXPECT_DATE,EXPECT_DATE1, ";
                mc.sSQL_Value = mc.sSQL_Value + " UPDATE_TIME,ORDER_STATUS) VALUES (@workorderid,GETDATE(), @workorder_type,@id, ";
                mc.sSQL_Value = mc.sSQL_Value + " @province,@city,@zipcode,@addr,@boxamount, ";
                mc.sSQL_Value = mc.sSQL_Value + " @telno ,@mobileno,@expect_date,@expect_date1, ";
                mc.sSQL_Value = mc.sSQL_Value + " GETDATE(),@statusid)";

                mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                if (mc.strRt_Value != "0")
                {
                    throw new Exception(mc.strRt_Value);
                }
                if (is_addaddr)
                {
                    if (Check_Addresses(id, province, city, zipcode, addr) == true)
                        mc.strRt_Value = Set_Addresses("ADD", id, province, city, zipcode, addr, telno, mobileno, usrip);
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return mc.strRt_Value;
        }

        public String Add_Workorderbox(string workorderid, string boxno)
        {
            MemberClass mc = new MemberClass();
            boxno = boxno.Substring(0, boxno.Length - 1);
            string[] split = boxno.Split(',');
            if (split.Length > 0)
            {
                foreach (string a in split)
                {
                    mc.sSQL_Value = "INSERT INTO WORKORDER_BOX (WORKORDER_ID,BOX_ID,UPDATE_TIME) VALUES  ";
                    mc.sSQL_Value = mc.sSQL_Value + " ('" + workorderid + "','" + a + "',GETDATE()) ";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                    if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                    mc.strRt_Value = "0";
                }
            }
            return mc.strRt_Value;
        }

        public String Update_Boxes(string boxno, string statusid, string workorderid)
        {
            MemberClass mc = new MemberClass();
            if (boxno.Length > 0)
            {
                if (boxno.IndexOf(',') == -1)
                {
                    mc.sSQL_Value = "UPDATE BOXES SET STATUS_ID = '" + statusid + "' WHERE BOX_NO = '" + boxno + "'";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                    if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                    mc.sSQL_Value = string.Empty;
                    mc.sSQL_Value = "INSERT INTO BOX_HISTORY (BOX_ID,STATUS_ID,STATUS_TIME,WORKORDER_ID,BOX_DESC) VALUES ";
                    mc.sSQL_Value = mc.sSQL_Value + " ('" + boxno + "','" + statusid + "',GETDATE(),'" + workorderid + "','" + Get_BoxdescByBOXID(boxno) + "')";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                    if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                }
                else
                {
                    string[] split = boxno.Split(',');
                    //String desc = Get_BoxdescByBOXID(boxno);
                    if (split.Length > 0)
                    {
                        foreach (string a in split)
                        {
                            mc.sSQL_Value = "UPDATE BOXES SET STATUS_ID = '" + statusid + "' WHERE BOX_NO = '" + a + "'";
                            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                            mc.sSQL_Value = string.Empty;
                            mc.sSQL_Value = "INSERT INTO BOX_HISTORY (BOX_ID,STATUS_ID,STATUS_TIME,WORKORDER_ID,BOX_DESC) VALUES ";
                            mc.sSQL_Value = mc.sSQL_Value + " ('" + a + "','" + statusid + "',GETDATE(),'" + workorderid + "','" + Get_BoxdescByBOXID(a) + "')";
                            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                            //2014/08/19 下午會議中,耀民提出這部份由後台入庫上架後再寫入
                            //if (statusid == "97")
                            //{
                            //    mc.sSQL_Value = string.Empty;
                            //    //如果是取消出库,箱子的狀態會先變成取消出库97,然後接著箱子狀態再變成在庫60
                            //    statusid = "60";
                            //    mc.sSQL_Value = "INSERT INTO BOX_HISTORY (BOX_ID,STATUS_ID,STATUS_TIME,WORKORDER_ID,BOX_DESC) VALUES ";
                            //    mc.sSQL_Value = mc.sSQL_Value + " ('" + a + "','" + statusid + "',GETDATE(),'" + workorderid + "','" + desc + "')";
                            //    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                            //    if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
                            //}
                        }
                    }
                }


            }

            return mc.strRt_Value;
        }

        public String Get_BoxdescByBOXID(string boxid)
        {
            MemberClass mc = new MemberClass();
            DataTable dt = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@boxid", boxid) };

            try
            {
                mc.sSQL_Value = "SELECT BOX_DESC FROM BOXES  with (nolock)  WHERE BOX_NO = @boxid ";
                dt = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                foreach (DataRow dr in dt.Rows)
                {
                    mc.strRt_Value = dr["BOX_DESC"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
            return mc.strRt_Value;
        }

        public String Update_Workorder(string workorderid, string statusid)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@workorderid", workorderid),
                new SqlParameter("@statusid",statusid)
            };
            mc.sSQL_Value = "UPDATE WORKORDER SET ORDER_STATUS = @statusid WHERE WORKORDER_ID = @workorderid ";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        public String Modify_BoxContentItem(string itemid, string itemtype, string itemdesc, string itemimage)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@itemid", itemid), 
                new SqlParameter("@itemtype", itemtype),
                new SqlParameter("@itemdesc",itemdesc.Trim()),
                new SqlParameter("@itemimage",itemimage)
            };
            mc.sSQL_Value = "UPDATE BOX_CONTENTS SET ITEM_TYPE = @itemtype,ITEM_DESC = @itemdesc,IMAGE_PATH = @itemimage ";
            mc.sSQL_Value = mc.sSQL_Value + " ,UPDATE_TIME = GETDATE() WHERE ID = @itemid ";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        /// <summary>
        /// 修改箱子描述
        /// </summary>
        /// <param name="boxno">箱號</param>
        /// <param name="boxdesc">箱子描述</param>
        /// <returns></returns>
        public String Modify_Boxdesc(string boxno, string boxdesc)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@boxno", boxno), 
                new SqlParameter("@boxdesc", boxdesc.Trim())
            };
            mc.sSQL_Value = "UPDATE BOXES SET BOX_DESC = @boxdesc WHERE BOX_NO = @boxno ";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        /// <summary>
        /// 清除箱內物品资料
        /// </summary>
        /// <param name="boxid">箱號</param>
        /// <returns></returns>
        public String Clear_Boxcontent(string boxid)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = { new SqlParameter("@boxid", boxid) };
            mc.sSQL_Value = "DELETE FROM BOX_CONTENTS WHERE ACCOUNT_BOX_ID = @boxid";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        public String Clear_BoxcontentByItem(string itemid)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = { new SqlParameter("@itemid", itemid) };
            mc.sSQL_Value = "DELETE FROM BOX_CONTENTS WHERE ID = @itemid";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        /// <summary>
        /// 寫入箱內资料
        /// </summary>
        /// <param name="boxid">箱號</param>
        /// <param name="itemtype">類別</param>
        /// <param name="itmedesc">描述</param>
        /// <param name="imagepath">圖片路徑</param>
        /// <returns></returns>
        public String Add_Boxcontent(string boxid, string itemtype, string itmedesc, string imagepath)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] parameters = 
            { 
                new SqlParameter("@boxid", boxid), 
                new SqlParameter("@itemtype", itemtype),
                new SqlParameter("@itmedesc", itmedesc),
                new SqlParameter("@imagepath", imagepath)
            };
            mc.sSQL_Value = "INSERT INTO BOX_CONTENTS (ACCOUNT_BOX_ID,ITEM_TYPE,ITEM_DESC,IMAGE_PATH,UPDATE_TIME) ";
            mc.sSQL_Value = mc.sSQL_Value + " VALUES (@boxid,@itemtype,@itmedesc,@imagepath,GETDATE())";
            mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
            if (mc.strRt_Value != "0") { throw new Exception(mc.strRt_Value); }
            mc.strRt_Value = "0";
            return mc.strRt_Value;
        }

        /// <summary>
        /// 修改订单
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="workorderid">订单編唬</param>
        /// <param name="expect_date">期望送達日期</param>
        /// <param name="province">州</param>
        /// <param name="city">市</param>
        /// <param name="addr">地址</param>
        /// <param name="zipcode">邮递区号</param>
        /// <param name="mobileno">手机</param>
        /// <param name="telno">家用电话</param>
        /// <param name="is_addaddr">是否加入常用地址</param>
        /// <param name="usrip">IP位址</param>
        /// <returns></returns>
        public String Set_Workorder(string id, string workorderid, string expect_date, string expect_date1, string province, string city,
            string addr, string zipcode, string mobileno, string telno, bool is_addaddr, string usrip)
        {
            MemberClass mc = new MemberClass();
            DataTable dt = new DataTable();
            SqlParameter[] param1 = { new SqlParameter("@workorderid", workorderid) };
            try
            {
                mc.sSQL_Value = "SELECT WORKORDER_ID FROM WORKORDER WHERE WORKORDER_ID = @workorderid ";
                dt = oDB.ExecuteDT(mc.sSQL_Value, param1);
                if (dt.Rows.Count == 0)
                {
                    mc.strRt_Value = "-3";
                    return mc.strRt_Value;
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }

            //如果送空箱日期為空值,為修改出庫訂單
            if (expect_date == "")
            {
                //修改出庫訂單
                SqlParameter[] param2 = 
            { 
                new SqlParameter("@workorderid", workorderid),
                new SqlParameter("@expect_date1", expect_date1),
                new SqlParameter("@province", province),
                new SqlParameter("@city", city),
                new SqlParameter("@addr", addr),
                new SqlParameter("@zipcode", zipcode),
                new SqlParameter("@mobileno", mobileno),
                new SqlParameter("@telno", telno)
            };
                try
                {
                    mc.sSQL_Value = string.Empty;
                    //修改出庫訂單
                    mc.sSQL_Value = "UPDATE WORKORDER SET EXPECT_DATE1 = @expect_date1, PROVINCE = @province ,CITY = @city,ADDR = @addr, ZIPCODE = @zipcode, ";
                    mc.sSQL_Value = mc.sSQL_Value + " MOBILE_NO = @mobileno, TEL_NO = @telno , UPDATE_TIME = GETDATE() ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = @workorderid";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param2);

                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    if (is_addaddr)
                    {
                        if (Check_Addresses(id, province, city, zipcode, addr) == true)
                            mc.strRt_Value = Set_Addresses("ADD", id, province, city, zipcode, addr, telno, mobileno, usrip);
                    }
                }
                catch (Exception ex) { throw new Exception(ex.ToString()); }

            }
            else
            {
                //修改入庫訂單
                SqlParameter[] param3 = 
            { 
                new SqlParameter("@workorderid", workorderid),
                new SqlParameter("@expect_date", expect_date),
                new SqlParameter("@expect_date1", expect_date1),
                new SqlParameter("@province", province),
                new SqlParameter("@city", city),
                new SqlParameter("@addr", addr),
                new SqlParameter("@zipcode", zipcode),
                new SqlParameter("@mobileno", mobileno),
                new SqlParameter("@telno", telno)
            };
                try
                {
                    //修改入庫訂單
                    mc.sSQL_Value = "UPDATE WORKORDER SET EXPECT_DATE = @expect_date,EXPECT_DATE1 = @expect_date1, PROVINCE = @province ,CITY = @city,ADDR = @addr, ZIPCODE = @zipcode, ";
                    mc.sSQL_Value = mc.sSQL_Value + " MOBILE_NO = @mobileno, TEL_NO = @telno , UPDATE_TIME = GETDATE() ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = @workorderid";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param3);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                    if (is_addaddr)
                    {
                        if (Check_Addresses(id, province, city, zipcode, addr) == true)
                            mc.strRt_Value = Set_Addresses("ADD", id, province, city, zipcode, addr, telno, mobileno, usrip);
                    }
                }
                catch (Exception ex) { throw new Exception(ex.ToString()); }
            }

            return mc.strRt_Value;
        }

        /// <summary>
        /// 查看订单
        /// </summary>
        /// <param name="workorderid">订单编号</param>
        /// <returns></returns>
        public DataTable Get_Workorder(string workorderid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@workorderid", workorderid) };
            try
            {
                //mc.sSQL_Value = "SELECT WORKORDER_ID,EXPECT_DATE1,PROVINCE,CITY,ADDR,ZIPCODE,MOBILE_NO FROM WORKORDER with (nolock)  ";
                //mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = @workorderid";
                mc.sSQL_Value = "SELECT A.WORKORDER_ID,CONVERT(VARCHAR(10),A.EXPECT_DATE,23) AS EXPECT_DATE,CONVERT(VARCHAR(10),A.EXPECT_DATE1,23) AS EXPECT_DATE1,A.PROVINCE,A.CITY,A.ADDR,A.ZIPCODE,A.MOBILE_NO,A.TEL_NO, ";
                mc.sSQL_Value = mc.sSQL_Value + "B.BOX_FIRST_IMAGE,B.BOX_ID,B.BOX_DESC FROM WORKORDER AS A with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + "LEFT OUTER JOIN WORKORDER_BOX AS B with (nolock)ON A.WORKORDER_ID = B.WORKORDER_ID ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE A.WORKORDER_ID = @workorderid";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }


        /// <summary>
        /// 取得订单编号(依日期條件)
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="startdate">開始日期</param>
        /// <param name="enddate">結束日期</param>
        /// <returns></returns>
        public DataTable Get_Workorderid(string id, string startdate, string enddate)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT WORKORDER_ID,CONVERT(VARCHAR(10),UPDATE_TIME,23) AS UPDATE_TIME,ORDER_STATUS FROM WORKORDER with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id AND (UPDATE_TIME BETWEEN ";
                mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        /// <summary>
        /// 查詢物品清单 V0.3
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="statusid">箱子狀態</param>
        /// <param name="startdate">開始日期</param>
        /// <param name="enddate">結束日期</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable Get_Workorderbox_V3_Search(string id, string statusid, string startdate, string enddate, string keyword)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            String sDefault_IMAGEPATH = System.Configuration.ConfigurationSettings.AppSettings["DEFAULT_IMAGE_PATH"];
            table.Columns.Add("IMAGE", typeof(string));
            table.Columns.Add("BOX_DESC", typeof(string));
            table.Columns.Add("UPDATE_TIME", typeof(string));
            table.Columns.Add("STATUS", typeof(string));
            table.Columns.Add("BOX_NO", typeof(string));
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                if (statusid == "30")
                {
                    mc.sSQL_Value = "select A.BOX_ID AS BOX_NO,CONVERT(VARCHAR(10),c.EXPECT_DATE,23) AS UPDATE_TIME,B.BOX_DESC,B.BOX_FIRST_IMAGE ";
                    mc.sSQL_Value = mc.sSQL_Value + "from ACCOUNT_BOXES AS A with (nolock), BOXES AS B , WORKORDER c , WORKORDER_BOX d ";
                    mc.sSQL_Value = mc.sSQL_Value + "where A.BOX_ID=B.BOX_NO and c.WORKORDER_ID = d.WORKORDER_ID ";
                    mc.sSQL_Value = mc.sSQL_Value + "and B.BOX_NO = d.BOX_ID ";
                    mc.sSQL_Value = mc.sSQL_Value + "and c.WORKORDER_TYPE = 'IN' and A.ACCOUNT_ID = @id and B.STATUS_ID IN (" + statusid + ")";
                    if (keyword != "" && (startdate == "" && enddate == ""))
                    { mc.sSQL_Value = mc.sSQL_Value + " AND B.BOX_DESC LIKE '%" + keyword + "%'"; }
                    else if ((startdate != "" && enddate != "") && keyword == "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND (c.EXPECT_DATE BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                    else if (keyword != "" && startdate != "" && enddate != "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND B.BOX_DESC LIKE '%" + keyword + "%'";
                        mc.sSQL_Value = mc.sSQL_Value + " AND (c.EXPECT_DATE BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                }

                if (statusid == "60" || statusid == "70")
                {
                    mc.sSQL_Value = "select A.BOX_ID AS BOX_NO,CONVERT(VARCHAR(10),A.LINK_TIME,23) AS UPDATE_TIME,B.BOX_DESC,B.BOX_FIRST_IMAGE ";
                    mc.sSQL_Value = mc.sSQL_Value + "from ACCOUNT_BOXES AS A with (nolock), BOXES AS B , WORKORDER c , WORKORDER_BOX d ";
                    mc.sSQL_Value = mc.sSQL_Value + "where A.BOX_ID=B.BOX_NO and c.WORKORDER_ID = d.WORKORDER_ID ";
                    mc.sSQL_Value = mc.sSQL_Value + "and B.BOX_NO = d.BOX_ID ";
                    mc.sSQL_Value = mc.sSQL_Value + "and c.WORKORDER_TYPE NOT IN ('IN','OUT') and A.ACCOUNT_ID = @id and B.STATUS_ID IN (" + statusid + ")";
                    if (keyword != "" && (startdate == "" && enddate == ""))
                    { mc.sSQL_Value = mc.sSQL_Value + " AND B.BOX_DESC LIKE '%" + keyword + "%'"; }
                    else if ((startdate != "" && enddate != "") && keyword == "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND (A.LINK_TIME BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                    else if (keyword != "" && startdate != "" && enddate != "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND B.BOX_DESC LIKE '%" + keyword + "%'";
                        mc.sSQL_Value = mc.sSQL_Value + " AND (A.LINK_TIME BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                }
                //mc.sSQL_Value = "select A.BOX_NO,A.UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE ";
                //mc.sSQL_Value = mc.sSQL_Value + " from BOXES AS A with (nolock) join ACCOUNT_BOXES AS B with (nolock) ";
                //mc.sSQL_Value = mc.sSQL_Value + " on A.BOX_NO=B.BOX_ID where B.ACCOUNT_ID = @id and A.STATUS_ID = @statusid ";


                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    foreach (DataRow dr1 in dT.Rows)
                    {
                        if (dr1["BOX_FIRST_IMAGE"].ToString() == "")
                        {
                            table.Rows.Add(sDefault_IMAGEPATH, dr1["BOX_DESC"].ToString(),
                            dr1["UPDATE_TIME"].ToString(), mc.sStatus_Name_Value(Convert.ToInt16(statusid)), dr1["BOX_NO"].ToString());
                        }
                        else
                        {
                            table.Rows.Add(dr1["BOX_FIRST_IMAGE"].ToString(), dr1["BOX_DESC"].ToString(),
                            dr1["UPDATE_TIME"].ToString(), mc.sStatus_Name_Value(Convert.ToInt16(statusid)), dr1["BOX_NO"].ToString());
                        }

                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }


        /// <summary>
        /// 查詢物品清单 V0.2
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="statusid">订单狀態</param>
        /// <param name="startdate">開始日期</param>
        /// <param name="enddate">結束日期</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable Get_Workorderbox_V2_Search(string id, string statusid, string startdate, string enddate, string keyword)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            table.Columns.Add("IMAGE", typeof(string));
            table.Columns.Add("BOX_DESC", typeof(string));
            table.Columns.Add("UPDATE_TIME", typeof(string));
            table.Columns.Add("STATUS", typeof(string));
            table.Columns.Add("BOX_NO", typeof(string));
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                DataTable dt2 = new DataTable();
                dT = Get_Workorderid(id, statusid);
                foreach (DataRow dr in dT.Rows)
                {
                    mc.sSQL_Value = "select A.WORKORDER_ID, A.BOX_ID,A.UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from WORKORDER_BOX AS A with (nolock) ";
                    mc.sSQL_Value = mc.sSQL_Value + " join BOXES AS B with (nolock) on A.BOX_ID = B.BOX_NO ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = '" + dr["WORKORDER_ID"].ToString() + "'";
                    if (keyword != "" && (startdate == "" && enddate == ""))
                    { mc.sSQL_Value = mc.sSQL_Value + " AND B.BOX_DESC LIKE '%" + keyword + "%'"; }
                    else if ((startdate != "" && enddate != "") && keyword == "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND (A.UPDATE_TIME BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                    else if (keyword != "" && startdate != "" && enddate != "")
                    {
                        mc.sSQL_Value = mc.sSQL_Value + " AND BOX_DESC LIKE '%" + keyword + "%'";
                        mc.sSQL_Value = mc.sSQL_Value + " AND (A.UPDATE_TIME BETWEEN ";
                        mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                    }
                    dt2 = oDB.ExecuteDT(mc.sSQL_Value);
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow dr1 in dt2.Rows)
                        {
                            table.Rows.Add(dr1["BOX_FIRST_IMAGE"].ToString(), dr1["BOX_DESC"].ToString(),
                                dr1["UPDATE_TIME"].ToString(), mc.sStatus_Name_Value(Convert.ToInt16(statusid)), dr1["BOX_ID"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }

        /// <summary>
        /// 查詢物品清单 V0.2
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="statusid">订单狀態</param>
        /// <returns></returns>
        public DataTable Get_Workorderbox_V2(string id, string statusid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            table.Columns.Add("IMAGE", typeof(string));
            table.Columns.Add("BOX_DESC", typeof(string));
            table.Columns.Add("UPDATE_TIME", typeof(string));
            table.Columns.Add("STATUS", typeof(string));
            table.Columns.Add("BOX_NO", typeof(string));
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                DataTable dt2 = new DataTable();
                dT = Get_Workorderid(id, statusid);
                foreach (DataRow dr in dT.Rows)
                {
                    mc.sSQL_Value = "select A.WORKORDER_ID, A.BOX_ID,A.UPDATE_TIME,A.BOX_DESC,A.BOX_FIRST_IMAGE from WORKORDER_BOX AS A with (nolock) ";
                    mc.sSQL_Value = mc.sSQL_Value + " join BOXES AS B with (nolock) on A.BOX_ID = B.BOX_NO ";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = '" + dr["WORKORDER_ID"].ToString() + "'";
                    dt2 = oDB.ExecuteDT(mc.sSQL_Value);
                    if (dt2.Rows.Count > 0)
                    {
                        foreach (DataRow dr1 in dt2.Rows)
                        {
                            table.Rows.Add(dr1["BOX_FIRST_IMAGE"].ToString(), dr1["BOX_DESC"].ToString(),
                                dr1["UPDATE_TIME"].ToString(), mc.sStatus_Name_Value(Convert.ToInt16(statusid)), dr1["BOX_ID"].ToString());
                        }
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }

        /// <summary>
        /// 取得订单编号
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <param name="statusid">订单狀態</param>
        /// <returns></returns>
        public DataTable Get_Workorderid(string id, string statusid)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id), new SqlParameter("@statusid", statusid) };
            try
            {
                mc.sSQL_Value = "SELECT WORKORDER_ID,CONVERT(VARCHAR(10),UPDATE_TIME,23) AS UPDATE_TIME,ORDER_STATUS FROM WORKORDER with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id AND ORDER_STATUS = @statusid ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }
        /// <summary>
        /// 取得订单编号
        /// </summary>
        /// <param name="id">會員ID</param>
        /// <returns></returns>
        public DataTable Get_Workorderid(string id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@id", id) };
            try
            {
                mc.sSQL_Value = "SELECT WORKORDER_ID,CONVERT(VARCHAR(10),UPDATE_TIME,23) AS UPDATE_TIME,ORDER_STATUS FROM WORKORDER with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        /// <summary>
        /// 依订单编号取得箱號(By关键字)
        /// </summary>
        /// <param name="workorder_id">订单编号</param>
        /// <param name="keyword">关键字</param>
        /// <returns></returns>
        public DataTable Get_Workorderbox(string workorder_id, string keyword)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@workorder_id", workorder_id) };
            try
            {
                mc.sSQL_Value = "SELECT BOX_ID,BOX_DESC,BOX_FIRST_IMAGE FROM WORKORDER_BOX with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = @workorder_id ";
                mc.sSQL_Value = mc.sSQL_Value + " AND BOX_DESC LIKE '%" + keyword + "%'";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        /// <summary>
        /// 依订单编号取得箱號
        /// </summary>
        /// <param name="workorder_id">订单编号</param>
        /// <returns></returns>
        public DataTable Get_Workorderbox(string workorder_id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@workorder_id", workorder_id) };
            try
            {
                mc.sSQL_Value = "SELECT BOX_ID,BOX_DESC,BOX_FIRST_IMAGE FROM WORKORDER_BOX with (nolock)  ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE WORKORDER_ID = @workorder_id";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        #endregion


        public DataTable Get_WxToken()
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                mc.sSQL_Value = "SELECT * FROM ctoken with (nolock) ";
                dT = oDB.ExecuteDT(mc.sSQL_Value);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        public String Set_WxToken(String appId,String token,String addTimestamp)
        {
            MemberClass mc = new MemberClass();
            SqlParameter[] param = 
            { 
                new SqlParameter("@appid", appId),
                new SqlParameter("@access_token", token),
                new SqlParameter("@addTimestamp", addTimestamp)
            };
            try
            {
                //修改入庫訂單
                mc.sSQL_Value = "UPDATE ctoken SET access_token = @access_token,addTimestamp = @addTimestamp ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE appId = @appid";
                mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, param);
                if (mc.strRt_Value != "0")
                {
                    throw new Exception(mc.strRt_Value);
                }
                else
                    return mc.strRt_Value;
                
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
        }


        /// <summary>
        /// 取得儲值方案
        /// </summary>
        /// <returns></returns>
        public DataTable Get_Creditpacks()
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                mc.sSQL_Value = "SELECT * FROM CREDIT_PACKS with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + " where IS_ACTIVE = 'Y' and ACTIVE_TIME < GETDATE() and DEACTIVE_TIME > GETDATE()";
                dT = oDB.ExecuteDT(mc.sSQL_Value);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }
        /// <summary>
        /// 取得付款方式
        /// </summary>
        /// <returns></returns>
        public DataTable Get_Paymenttype()
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                mc.sSQL_Value = "SELECT * FROM PAYMENT_TYPES with (nolock) ";
                dT = oDB.ExecuteDT(mc.sSQL_Value);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }

        #endregion

        #region 订单管理

        /// <summary>
        /// 查詢入倉/出倉订单
        /// </summary>
        /// <param name="id"></param>
        /// <param name="logintype"></param>
        /// <param name="sToken"></param>
        /// <param name="ordertype"></param>
        /// <param name="startdate"></param>
        /// <param name="enddate"></param>
        /// <returns></returns>
        public DataTable Get_Workorder_BySearch(string id, string ordertype, string statusid, string startdate, string enddate)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            DataTable table = new DataTable();
            table.Columns.Add("WORKORDER_ID", typeof(string));
            table.Columns.Add("EXPECT_DATE", typeof(string));
            table.Columns.Add("EXPECT_DATE1", typeof(string));
            table.Columns.Add("ORDER_STATUS", typeof(string));
            table.Columns.Add("BOX_AMOUNT", typeof(string));
            SqlParameter[] parameters = 
            {
                new SqlParameter("@id", id), 
                new SqlParameter("@ordertype", ordertype),
                new SqlParameter("@statusid",statusid)
            };
            try
            {
                //mc.sSQL_Value = "SELECT WORKORDER_ID,CONVERT(VARCHAR(10),UPDATE_TIME,23) AS UPDATE_TIME,ORDER_STATUS,BOX_AMOUNT FROM WORKORDER with (nolock) ";
                mc.sSQL_Value = "SELECT WORKORDER_ID,CONVERT(VARCHAR(10),EXPECT_DATE,23) AS EXPECT_DATE,CONVERT(VARCHAR(10),EXPECT_DATE1,23) AS EXPECT_DATE1,ORDER_STATUS,BOX_AMOUNT FROM WORKORDER with (nolock) ";
                mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = @id ";
                if (statusid == "" && ordertype == "IN")
                {
                    mc.sSQL_Value = mc.sSQL_Value + " and WORKORDER_TYPE in ('IN','RETRIEVE') ";
                    mc.sSQL_Value = mc.sSQL_Value + " AND (ORDER_STATUS = 0 OR ORDER_STATUS = 10 OR ORDER_STATUS = 30)";
                }
                else if (statusid == "" && ordertype == "OUT")
                {
                    mc.sSQL_Value = mc.sSQL_Value + " and WORKORDER_TYPE in ('OUT') ";
                    mc.sSQL_Value = mc.sSQL_Value + " AND (ORDER_STATUS = 70 OR ORDER_STATUS = 80 OR ORDER_STATUS = 90)";
                }
                else
                {
                    mc.sSQL_Value = mc.sSQL_Value + "AND ORDER_STATUS = @statusid";
                }

                if (startdate != "" && enddate != "")
                {
                    mc.sSQL_Value = mc.sSQL_Value + " AND (UPDATE_TIME BETWEEN ";
                    mc.sSQL_Value = mc.sSQL_Value + "'" + startdate + " 00:00:00' AND '" + enddate + " 23:59:59')";
                }
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count > 0)
                {
                    foreach (DataRow dr1 in dT.Rows)
                    {
                        table.Rows.Add(dr1["WORKORDER_ID"].ToString(), dr1["EXPECT_DATE"].ToString(), dr1["EXPECT_DATE1"].ToString()
                            , mc.sStatus_Name_Value(Convert.ToInt16(dr1["ORDER_STATUS"])), dr1["BOX_AMOUNT"].ToString());
                    }
                }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return table;
        }
        #endregion

        public string add_sms(string mobileno, string smsbody)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                SqlParameter[] parameters = { new SqlParameter("@mobileno", mobileno), new SqlParameter("@smsbody", smsbody) };
                mc.sSQL_Value = "insert into SMS_DATA (MOBILE_NO,SMS_BODY,CREATE_TIME)";
                mc.sSQL_Value = mc.sSQL_Value + " VALUES (@mobileno,@smsbody,GETDATE())";
                mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                if (mc.strRt_Value != "0")
                {
                    throw new Exception(mc.strRt_Value);
                }
            }
            catch (Exception ex) {throw new Exception(ex.ToString()); }
                 
            return mc.strRt_Value;
        }

        public DataTable Get_SMSDATA()
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            
            try
            {
                mc.sSQL_Value = "select MOBILE_NO,SMS_BODY from SMS_DATA  with (nolock) where SEND_TIME is null  ";
                dT = oDB.ExecuteDT(mc.sSQL_Value);
                if (dT.Rows.Count <= 0) { dT = null; }
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            return dT;
        }




        //匯款帳號設定
        public String Set_ACCOUNT_BANK(string action, string account_id, string type, string bankname, string bankaccount, string bankaccountname)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            try
            {
                
                if (action == "ADD")
                {
                    //註記原設定帳號為N
                    mc.sSQL_Value = "UPDATE ACCOUNT_BANK SET IS_ACTIVE = 'N', ";
                    mc.sSQL_Value = mc.sSQL_Value + " UPDATE_TIME = GETDATE()";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = '" + account_id + "'";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }

                    if (type == "alipay")
                    {
                        SqlParameter[] parameters = 
                        {
                            new SqlParameter("@type", type),
                            new SqlParameter("@bankaccountname", bankaccountname),
                            new SqlParameter("@account_id", account_id)
                        };
                        mc.sSQL_Value = "INSERT INTO ACCOUNT_BANK";
                        mc.sSQL_Value = mc.sSQL_Value + "(TYPE,BANK_ACCOUNT_NAME,ACCOUNT_ID,IS_ACTIVE,CREATE_TIME,UPDATE_TIME )";
                        mc.sSQL_Value = mc.sSQL_Value + " VALUES (@type,@bankaccountname,@account_id,'Y',getdate(),getdate())";
                        mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    }
                    else
                    {
                        SqlParameter[] parameters = 
                        {
                            new SqlParameter("@type", type),
                            new SqlParameter("@bankname", bankname),
                            new SqlParameter("@bankaccount", bankaccount),
                            new SqlParameter("@bankaccountname", bankaccountname),
                            new SqlParameter("@account_id", account_id)
                        };
                        mc.sSQL_Value = "INSERT INTO ACCOUNT_BANK";
                        mc.sSQL_Value = mc.sSQL_Value + "(TYPE,BANK_NAME,BANK_ACCOUNT,BANK_ACCOUNT_NAME,ACCOUNT_ID,IS_ACTIVE,CREATE_TIME,UPDATE_TIME )";
                        mc.sSQL_Value = mc.sSQL_Value + " VALUES (@type,@bankname,@bankaccount,@bankaccountname,@account_id,'Y',getdate(),getdate())";
                        mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value, parameters);
                    }

                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }
                if (action == "DELETE")
                {
                    //註記原設定帳號為N
                    mc.sSQL_Value = "UPDATE ACCOUNT_BANK SET IS_ACTIVE = 'N', ";
                    mc.sSQL_Value = mc.sSQL_Value + " UPDATE_TIME = GETDATE()";
                    mc.sSQL_Value = mc.sSQL_Value + " WHERE ACCOUNT_ID = '" + account_id + "'";
                    mc.strRt_Value = oDB.ExecuteNonQuery(mc.sSQL_Value);
                    if (mc.strRt_Value != "0")
                    {
                        throw new Exception(mc.strRt_Value);
                    }
                }

                return mc.strRt_Value;
            }
            catch (Exception ex) { return ex.ToString(); }
        }

        public DataTable Get_ACCOUNT_BANK(string account_id)
        {
            MemberClass mc = new MemberClass();
            DataTable dT = new DataTable();
            SqlParameter[] parameters = { new SqlParameter("@account_id", account_id) };
            try
            {
                mc.sSQL_Value = "SELECT TYPE,BANK_NAME,BANK_ACCOUNT,BANK_ACCOUNT_NAME,ACCOUNT_ID from ACCOUNT_BANK  with (nolock) WHERE (ACCOUNT_ID = @account_id) AND IS_ACTIVE = 'Y' ";
                dT = oDB.ExecuteDT(mc.sSQL_Value, parameters);
                if (dT.Rows.Count <= 0) { dT = null; }
                return dT;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());
            }
        }

    }
}
